<?php
	class FarmAgent extends DAO {
		
		protected $_field_ts_map;
		
		public function __construct() {
			parent::__construct();
			$this->_field_ts_map = array(
				'date_planted_ts'					=>	'fi',
				'cc_date_planted_ts'			=>	'cc',
				'cc_term_date_ts'					=>	'cc_term',
				'pc_application_date_ts'	=>	'pc_app'
			);
		}
		
		public function saveNewFarm($farm, $user_email) {
			// prevent SQL injection & escape reserved characters
			foreach($farm as &$f) {
				if(is_array($f)) foreach($f as &$k) $k = $this->getEscapedSQLString(trim($k));
				else $f =  $this->getEscapedSQLString(trim($f));
			}

			// prepare fields
			$name = "'{$farm['name']}'";
			$created_by = "'{$user_email}'";
			$contact_fn = "'{$farm['contact_fn']}'";
			$contact_ln = "'{$farm['contact_ln']}'";
			$contact_phone = empty($farm['contact_phone']) ? 'NULL' : "'{$farm['contact_phone']}'";
			$contact_mobile = empty($farm['contact_mobile']) ? 'NULL' : "'{$farm['contact_mobile']}'";
			$contact_email = empty($farm['contact_email']) ? 'NULL' : "'{$farm['contact_email']}'";
			$address = empty($farm['address']) ? 'NULL' : "'{$farm['address']}'";
			$city = empty($farm['city']) ? 'NULL' : "'{$farm['city']}'";
			$state = empty($farm['state']) ? 'NULL' : "'{$farm['state']}'";
			$zip = empty($farm['zip']) ? 'NULL' : "'{$farm['zip']}'";
			$county = empty($farm['county']) ? 'NULL' : "'{$farm['county']}'";
			$basin = empty($farm['basin']) ? 'NULL' : "'{$farm['basin']}'";
			$huc = empty($farm['huc']) ? 'NULL' : "'{$farm['huc']}'";
			$this->query("
				INSERT INTO farm (name,created_by,created_ts,last_mod_by,last_mod_ts,owned_by,contact_fn,contact_ln,contact_phone,contact_mobile,contact_email,address,city,state,zip,county,basin,huc)
				VALUES ($name,$created_by,UNIX_TIMESTAMP(),$created_by,UNIX_TIMESTAMP(),$created_by,$contact_fn,$contact_ln,$contact_phone,$contact_mobile,$contact_email,$address,$city,$state,$zip,$county,$basin,$huc)
			");
			if(!$this->insertOK()) return false;
			
			// insert fields of farm now
			$farm_id = $this->getInsertId();
			$field_cnt = count($farm['field_name']);
			$sql = 'INSERT INTO field (
				farm_id,field_name,year,acres,herbicide_used,preceding_crop,current_crop,date_planted_ts,lime_applied,comments,
				f_N_PSNT,f_brayp1,f_K,f_NH4,f_soilph,f_bufferedph,f_comments,
				na_N,na_P,na_K,na_manure,na_type,na_mN,na_mP,na_mK,na_comments,
				cc_family,cc_used,cc_rate,cc_seed_cost,cc_cost_share,cc_planting_method,cc_date_planted_ts,cc_fall_quality,cc_spring_quality,cc_term_method,cc_term_chem,cc_carrier,cc_spray_count,cc_term_temp,cc_term_weather,cc_term_date_ts,cc_term_success,cc_yield,cc_comments,
				ct_acres,ct_previous,ct_current,ct_comments,
				pc_pest,pc_threshold,pc_method,pc_CAP,pc_application_date_ts,pc_comments
			) VALUES ';
			for($i = 0; $i < $field_cnt; $i++) {
				// prepare fields
				$field_name = "'{$farm['field_name'][$i]}'";
				$year = $farm['year'][$i];
				$acres = preg_replace('/[^\d+]/','',$farm['acres'][$i]);
				$acres = empty($acres) ? 'NULL' : intval($acres);
				$herbicide_used = empty($farm['herbicide_used'][$i]) ? 'NULL' : "'{$farm['herbicide_used'][$i]}'";
				$preceding_crop = empty($farm['preceding_crop'][$i]) ? 'NULL' : "'{$farm['preceding_crop'][$i]}'";
				$current_crop = empty($farm['current_crop'][$i]) ? 'NULL' : "'{$farm['current_crop'][$i]}'";
				$date_planted_ts = strtotime("{$farm['fi_month'][$i]}/{$farm['fi_day'][$i]}/{$farm['fi_year'][$i]}");
				$lime_applied = empty($farm['lime_applied'][$i]) ? 'NULL' : "'{$farm['lime_applied'][$i]}'";
				$comments = empty($farm['comments'][$i]) ? 'NULL' : "'{$farm['comments'][$i]}'";
				$f_N_PSNT = empty($farm['f_npsnt'][$i]) ? 'NULL' : "'{$farm['f_npsnt'][$i]}'";
				$f_brayp1 = empty($farm['f_brayp1'][$i]) ? 'NULL' : "'{$farm['f_brayp1'][$i]}'";
				$f_K = empty($farm['f_K'][$i]) ? 'NULL' : "'{$farm['f_K'][$i]}'";
				$f_NH4 = empty($farm['f_nh4'][$i]) ? 'NULL' : "'{$farm['f_nh4'][$i]}'";
				$f_soilph = empty($farm['f_soilph'][$i]) ? 'NULL' : "'{$farm['f_soilph'][$i]}'";
				$f_bufferedph = empty($farm['f_bufferedph'][$i]) ? 'NULL' : "'{$farm['f_bufferedph'][$i]}'";
				$f_comments = empty($farm['f_comments'][$i]) ? 'NULL' : "'{$farm['f_comments'][$i]}'";
				$na_N = preg_replace('/[^\d+]/','',$farm['na_N'][$i]);
				$na_N = empty($na_N) ? 'NULL' : intval($na_N);
				$na_P = preg_replace('/[^\d+]/','',$farm['na_P'][$i]);
				$na_P = empty($na_P) ? 'NULL' : intval($na_P);
				$na_K = preg_replace('/[^\d+]/','',$farm['na_K'][$i]);
				$na_K = empty($na_K) ? 'NULL' : intval($na_K);
				$na_manure = "'{$farm['na_manure'][$i]}'";
				$na_type = empty($farm['na_type'][$i]) ? 'NULL' : "'{$farm['na_type'][$i]}'";
				$na_mN = preg_replace('/[^\d+]/','',$farm['na_mN'][$i]);
				$na_mN = empty($na_mN) ? 'NULL' : intval($na_mN);
				$na_mP = preg_replace('/[^\d+]/','',$farm['na_mP'][$i]);
				$na_mP = empty($na_mP) ? 'NULL' : intval($na_mP);
				$na_mK = preg_replace('/[^\d+]/','',$farm['na_mK'][$i]);
				$na_mK = empty($na_mK) ? 'NULL' : intval($na_mK);
				$na_comments = empty($farm['na_comments'][$i]) ? 'NULL' : "'{$farm['na_comments'][$i]}'";
				$cc_family = empty($farm['cc_family'][$i]) ? 'NULL' : "'{$farm['cc_family'][$i]}'";
				$cc_used = empty($farm['cc_used'][$i]) ? 'NULL' : "'{$farm['cc_used'][$i]}'";
				$cc_rate = preg_replace('/[^\d+]/','',$farm['cc_rate'][$i]);
				$cc_rate = empty($cc_rate) ? 'NULL' : intval($cc_rate);
				$cc_seed_cost = floatval(str_replace('$','',str_replace(',','',$farm['cc_seed_cost'][$i])));
				if(empty($cc_seed_cost)) $cc_seed_cost = 'NULL';
				$cc_cost_share = empty($farm['cc_cost_share'][$i]) ? 'NULL' : "'{$farm['cc_cost_share'][$i]}'";
				$cc_planting_method = empty($farm['cc_planting_method'][$i]) ? 'NULL' : "'{$farm['cc_planting_method'][$i]}'";
				$cc_date_planted_ts = strtotime("{$farm['cc_month'][$i]}/{$farm['cc_day'][$i]}/{$farm['cc_year'][$i]}");
				$cc_fall_quality = empty($farm['cc_quality_fall'][$i]) ? 'NULL' : $farm['cc_quality_fall'][$i];
				$cc_spring_quality = empty($farm['cc_quality_spring'][$i]) ? 'NULL' : $farm['cc_quality_spring'][$i];
				$cc_term_method = empty($farm['cc_term_method'][$i]) ? 'NULL' : "'{$farm['cc_term_method'][$i]}'";
				$cc_term_chem = empty($farm['cc_term_chem'][$i]) ? 'NULL' : "'{$farm['cc_term_chem'][$i]}'";
				$cc_carrier = empty($farm['cc_carrier'][$i]) ? 'NULL' : "'{$farm['cc_carrier'][$i]}'";
				$cc_spray_count = empty($farm['cc_sprayed'][$i]) ? 'NULL' : $farm['cc_sprayed'][$i];
				$cc_term_temp = preg_replace('/[^\d+]/','',$farm['cc_term_temp'][$i]);
				$cc_term_temp = empty($cc_term_temp) ? 'NULL' : intval($cc_term_temp);
				$cc_term_weather = empty($farm['cc_term_weather'][$i]) ? 'NULL' : "'{$farm['cc_term_weather'][$i]}'";
				$cc_term_date_ts = strtotime("{$farm['cc_term_month'][$i]}/{$farm['cc_term_day'][$i]}/{$farm['cc_term_year'][$i]}");
				$cc_term_success = empty($farm['cc_success_term'][$i]) ? 'NULL' : "'{$farm['cc_success_term'][$i]}'";
				$cc_yield = preg_replace('/[^\d+]/','',$farm['cc_yield'][$i]);
				$cc_yield = empty($cc_yield) ? 'NULL' : intval($cc_yield);
				$cc_comments = empty($farm['cc_comments'][$i]) ? 'NULL' : "'{$farm['cc_comments'][$i]}'";
				$ct_acres = preg_replace('/[^\d+]/','',$farm['ct_acres'][$i]);
				$ct_acres = empty($ct_acres) ? 'NULL' : intval($ct_acres);
				$ct_previous = empty($farm['ct_previous'][$i]) ? 'NULL' : "'{$farm['ct_previous'][$i]}'";
				$ct_current = empty($farm['ct_current'][$i]) ? 'NULL' : "'{$farm['ct_current'][$i]}'";
				$ct_comments = empty($farm['ct_comments'][$i]) ? 'NULL' : "'{$farm['ct_comments'][$i]}'";
				$pc_pest = empty($farm['pc_pest'][$i]) ? 'NULL' : "'{$farm['pc_pest'][$i]}'";
				$pc_threshold = empty($farm['pc_threshold'][$i]) ? 'NULL' : "'{$farm['pc_threshold'][$i]}'";
				$pc_method = empty($farm['pc_method'][$i]) ? 'NULL' : "'{$farm['pc_method'][$i]}'";
				$pc_CAP = empty($farm['pc_chem_app_rate'][$i]) ? 'NULL' : "'{$farm['pc_chem_app_rate'][$i]}'";
				$pc_application_date_ts = strtotime("{$farm['pc_app_month'][$i]}/{$farm['pc_app_day'][$i]}/{$farm['pc_app_year'][$i]}");
				$pc_comments = empty($farm['pc_comments'][$i]) ? 'NULL' : "'{$farm['pc_comments'][$i]}'";
				$sql .= "(
					$farm_id,$field_name,$year,$acres,$herbicide_used,$preceding_crop,$current_crop,$date_planted_ts,$lime_applied,$comments,
					$f_N_PSNT,$f_brayp1,$f_K,$f_NH4,$f_soilph,$f_bufferedph,$f_comments,
					$na_N,$na_P,$na_K,$na_manure,$na_type,$na_mN,$na_mP,$na_mK,$na_comments,
					$cc_family,$cc_used,$cc_rate,$cc_seed_cost,$cc_cost_share,$cc_planting_method,$cc_date_planted_ts,$cc_fall_quality,$cc_spring_quality,$cc_term_method,$cc_term_chem,$cc_carrier,$cc_spray_count,$cc_term_temp,$cc_term_weather,$cc_term_date_ts,$cc_term_success,$cc_yield,$cc_comments,
					$ct_acres,$ct_previous,$ct_current,$ct_comments,
					$pc_pest,$pc_threshold,$pc_method,$pc_CAP,$pc_application_date_ts,$pc_comments
				),";
			}
			$sql = substr($sql, 0, -1);
			$this->query($sql);
			
			// return the newly created farm id on success, false otherwise
			if($this->insertOK()) return $farm_id;
			else return false;
		}
		
		public function saveField($field, $user_email) {
			// SQL injection and escaping
			foreach($field as &$f) $f = $this->getEscapedSQLString(trim($f));
			
			// prepare fields
			$last_mod_by = "'{$user_email}'";
			$farm_id = $field['farm_id'];
			
			$field_name = "'{$field['field_name']}'";
			$year = $field['year'];
			$acres = preg_replace('/[^\d+]/','',$field['acres']);
			$acres = empty($acres) ? 'NULL' : intval($acres);
			$herbicide_used = empty($field['herbicide_used']) ? 'NULL' : "'{$field['herbicide_used']}'";
			$preceding_crop = empty($field['preceding_crop']) ? 'NULL' : "'{$field['preceding_crop']}'";
			$current_crop = empty($field['current_crop']) ? 'NULL' : "'{$field['current_crop']}'";
			$date_planted_ts = strtotime("{$field['fi_month']}/{$field['fi_day']}/{$field['fi_year']}");
			$lime_applied = empty($field['lime_applied']) ? 'NULL' : "'{$field['lime_applied']}'";
			$comments = empty($field['comments']) ? 'NULL' : "'{$field['comments']}'";
			$f_N_PSNT = empty($field['f_npsnt']) ? 'NULL' : "'{$field['f_npsnt']}'";
			$f_brayp1 = empty($field['f_brayp1']) ? 'NULL' : "'{$field['f_brayp1']}'";
			$f_K = empty($field['f_K']) ? 'NULL' : "'{$field['f_K']}'";
			$f_NH4 = empty($field['f_nh4']) ? 'NULL' : "'{$field['f_nh4']}'";
			$f_soilph = empty($field['f_soilph']) ? 'NULL' : "'{$field['f_soilph']}'";
			$f_bufferedph = empty($field['f_bufferedph']) ? 'NULL' : "'{$field['f_bufferedph']}'";
			$f_comments = empty($field['f_comments']) ? 'NULL' : "'{$field['f_comments']}'";
			$na_N = preg_replace('/[^\d+]/','',$field['na_N']);
			$na_N = empty($na_N) ? 'NULL' : intval($na_N);
			$na_P = preg_replace('/[^\d+]/','',$field['na_P']);
			$na_P = empty($na_P) ? 'NULL' : intval($na_P);
			$na_K = preg_replace('/[^\d+]/','',$field['na_K']);
			$na_K = empty($na_K) ? 'NULL' : intval($na_K);
			$na_manure = "'{$field['na_manure']}'";
			$na_type = empty($field['na_type']) ? 'NULL' : "'{$field['na_type']}'";
			$na_mN = preg_replace('/[^\d+]/','',$field['na_mN']);
			$na_mN = empty($na_mN) ? 'NULL' : intval($na_mN);
			$na_mP = preg_replace('/[^\d+]/','',$field['na_mP']);
			$na_mP = empty($na_mP) ? 'NULL' : intval($na_mP);
			$na_mK = preg_replace('/[^\d+]/','',$field['na_mK']);
			$na_mK = empty($na_mK) ? 'NULL' : intval($na_mK);
			$na_comments = empty($field['na_comments']) ? 'NULL' : "'{$field['na_comments']}'";
			$cc_family = empty($field['cc_family']) ? 'NULL' : "'{$field['cc_family']}'";
			$cc_used = empty($field['cc_used']) ? 'NULL' : "'{$field['cc_used']}'";
			$cc_rate = preg_replace('/[^\d+]/','',$field['cc_rate']);
			$cc_rate = empty($cc_rate) ? 'NULL' : intval($cc_rate);
			$cc_seed_cost = floatval(str_replace('$','',str_replace(',','',$field['cc_seed_cost'])));
			if(empty($cc_seed_cost)) $cc_seed_cost = 'NULL';
			$cc_cost_share = empty($field['cc_cost_share']) ? 'NULL' : "'{$field['cc_cost_share']}'";
			$cc_planting_method = empty($field['cc_planting_method']) ? 'NULL' : "'{$field['cc_planting_method']}'";
			$cc_date_planted_ts = strtotime("{$field['cc_month']}/{$field['cc_day']}/{$field['cc_year']}");
			$cc_fall_quality = empty($field['cc_quality_fall']) ? 'NULL' : $field['cc_quality_fall'];
			$cc_spring_quality = empty($field['cc_quality_spring']) ? 'NULL' : $field['cc_quality_spring'];
			$cc_term_method = empty($field['cc_term_method']) ? 'NULL' : "'{$field['cc_term_method']}'";
			$cc_term_chem = empty($field['cc_term_chem']) ? 'NULL' : "'{$field['cc_term_chem']}'";
			$cc_carrier = empty($field['cc_carrier']) ? 'NULL' : "'{$field['cc_carrier']}'";
			$cc_spray_count = empty($field['cc_sprayed']) ? 'NULL' : $field['cc_sprayed'];
			$cc_term_temp = preg_replace('/[^\d+]/','',$field['cc_term_temp']);
			$cc_term_temp = empty($cc_term_temp) ? 'NULL' : intval($cc_term_temp);
			$cc_term_weather = empty($field['cc_term_weather']) ? 'NULL' : "'{$field['cc_term_weather']}'";
			$cc_term_date_ts = strtotime("{$field['cc_term_month']}/{$field['cc_term_day']}/{$field['cc_term_year']}");
			$cc_term_success = empty($field['cc_success_term']) ? 'NULL' : "'{$field['cc_success_term']}'";
			$cc_yield = preg_replace('/[^\d+]/','',$field['cc_yield']);
			$cc_yield = empty($cc_yield) ? 'NULL' : intval($cc_yield);
			$cc_comments = empty($field['cc_comments']) ? 'NULL' : "'{$field['cc_comments']}'";
			$ct_acres = preg_replace('/[^\d+]/','',$field['ct_acres']);
			$ct_acres = empty($ct_acres) ? 'NULL' : intval($ct_acres);
			$ct_previous = empty($field['ct_previous']) ? 'NULL' : "'{$field['ct_previous']}'";
			$ct_current = empty($field['ct_current']) ? 'NULL' : "'{$field['ct_current']}'";
			$ct_comments = empty($field['ct_comments']) ? 'NULL' : "'{$field['ct_comments']}'";
			$pc_pest = empty($field['pc_pest']) ? 'NULL' : "'{$field['pc_pest']}'";
			$pc_threshold = empty($field['pc_threshold']) ? 'NULL' : "'{$field['pc_threshold']}'";
			$pc_method = empty($field['pc_method']) ? 'NULL' : "'{$field['pc_method']}'";
			$pc_CAP = empty($field['pc_chem_app_rate']) ? 'NULL' : "'{$field['pc_chem_app_rate']}'";
			$pc_application_date_ts = strtotime("{$field['pc_app_month']}/{$field['pc_app_day']}/{$field['pc_app_year']}");
			$pc_comments = empty($field['pc_comments']) ? 'NULL' : "'{$field['pc_comments']}'";
			
			$this->query("
				INSERT INTO field (
					farm_id,field_name,year,acres,herbicide_used,preceding_crop,current_crop,date_planted_ts,lime_applied,comments,
					f_N_PSNT,f_brayp1,f_K,f_NH4,f_soilph,f_bufferedph,f_comments,
					na_N,na_P,na_K,na_manure,na_type,na_mN,na_mP,na_mK,na_comments,
					cc_family,cc_used,cc_rate,cc_seed_cost,cc_cost_share,cc_planting_method,cc_date_planted_ts,cc_fall_quality,cc_spring_quality,cc_term_method,cc_term_chem,cc_carrier,cc_spray_count,cc_term_temp,cc_term_weather,cc_term_date_ts,cc_term_success,cc_yield,cc_comments,
					ct_acres,ct_previous,ct_current,ct_comments,
					pc_pest,pc_threshold,pc_method,pc_CAP,pc_application_date_ts,pc_comments
				) VALUES (
					$farm_id,$field_name,$year,$acres,$herbicide_used,$preceding_crop,$current_crop,$date_planted_ts,$lime_applied,$comments,
					$f_N_PSNT,$f_brayp1,$f_K,$f_NH4,$f_soilph,$f_bufferedph,$f_comments,
					$na_N,$na_P,$na_K,$na_manure,$na_type,$na_mN,$na_mP,$na_mK,$na_comments,
					$cc_family,$cc_used,$cc_rate,$cc_seed_cost,$cc_cost_share,$cc_planting_method,$cc_date_planted_ts,$cc_fall_quality,$cc_spring_quality,$cc_term_method,$cc_term_chem,$cc_carrier,$cc_spray_count,$cc_term_temp,$cc_term_weather,$cc_term_date_ts,$cc_term_success,$cc_yield,$cc_comments,
					$ct_acres,$ct_previous,$ct_current,$ct_comments,
					$pc_pest,$pc_threshold,$pc_method,$pc_CAP,$pc_application_date_ts,$pc_comments
				)
			");
			if($this->insertOK()) {
				$this->query("
					UPDATE farm 
					SET last_mod_by=$last_mod_by, last_mod_ts=UNIX_TIMESTAMP() 
					WHERE farm_id=$farm_id
				");
				return true;
			} else return false;
		}
		
		public function updateField($field, $user_email) {
			// SQL injection and escaping
			foreach($field as &$f) $f = $this->getEscapedSQLString(trim($f));
			
			// prepare fields
			$last_mod_by = "'{$user_email}'";
			$field_name = "'{$field['field_name']}'";
			$year = $field['year'];
			$acres = preg_replace('/[^\d+]/','',$field['acres']);
			$acres = empty($acres) ? 'NULL' : intval($acres);
			$herbicide_used = empty($field['herbicide_used']) ? 'NULL' : "'{$field['herbicide_used']}'";
			$preceding_crop = empty($field['preceding_crop']) ? 'NULL' : "'{$field['preceding_crop']}'";
			$current_crop = empty($field['current_crop']) ? 'NULL' : "'{$field['current_crop']}'";
			$date_planted_ts = strtotime("{$field['fi_month']}/{$field['fi_day']}/{$field['fi_year']}");
			$lime_applied = empty($field['lime_applied']) ? 'NULL' : "'{$field['lime_applied']}'";
			$comments = empty($field['comments']) ? 'NULL' : "'{$field['comments']}'";
			$f_N_PSNT = empty($field['f_npsnt']) ? 'NULL' : "'{$field['f_npsnt']}'";
			$f_brayp1 = empty($field['f_brayp1']) ? 'NULL' : "'{$field['f_brayp1']}'";
			$f_K = empty($field['f_K']) ? 'NULL' : "'{$field['f_K']}'";
			$f_NH4 = empty($field['f_nh4']) ? 'NULL' : "'{$field['f_nh4']}'";
			$f_soilph = empty($field['f_soilph']) ? 'NULL' : "'{$field['f_soilph']}'";
			$f_bufferedph = empty($field['f_bufferedph']) ? 'NULL' : "'{$field['f_bufferedph']}'";
			$f_comments = empty($field['f_comments']) ? 'NULL' : "'{$field['f_comments']}'";
			$na_N = preg_replace('/[^\d+]/','',$field['na_N']);
			$na_N = empty($na_N) ? 'NULL' : intval($na_N);
			$na_P = preg_replace('/[^\d+]/','',$field['na_P']);
			$na_P = empty($na_P) ? 'NULL' : intval($na_P);
			$na_K = preg_replace('/[^\d+]/','',$field['na_K']);
			$na_K = empty($na_K) ? 'NULL' : intval($na_K);
			$na_manure = "'{$field['na_manure']}'";
			$na_type = empty($field['na_type']) ? 'NULL' : "'{$field['na_type']}'";
			$na_mN = preg_replace('/[^\d+]/','',$field['na_mN']);
			$na_mN = empty($na_mN) ? 'NULL' : intval($na_mN);
			$na_mP = preg_replace('/[^\d+]/','',$field['na_mP']);
			$na_mP = empty($na_mP) ? 'NULL' : intval($na_mP);
			$na_mK = preg_replace('/[^\d+]/','',$field['na_mK']);
			$na_mK = empty($na_mK) ? 'NULL' : intval($na_mK);
			$na_comments = empty($field['na_comments']) ? 'NULL' : "'{$field['na_comments']}'";
			$cc_family = empty($field['cc_family']) ? 'NULL' : "'{$field['cc_family']}'";
			$cc_used = empty($field['cc_used']) ? 'NULL' : "'{$field['cc_used']}'";
			$cc_rate = preg_replace('/[^\d+]/','',$field['cc_rate']);
			$cc_rate = empty($cc_rate) ? 'NULL' : intval($cc_rate);
			$cc_seed_cost = floatval(str_replace('$','',str_replace(',','',$field['cc_seed_cost'])));
			if(empty($cc_seed_cost)) $cc_seed_cost = 'NULL';
			$cc_cost_share = empty($field['cc_cost_share']) ? 'NULL' : "'{$field['cc_cost_share']}'";
			$cc_planting_method = empty($field['cc_planting_method']) ? 'NULL' : "'{$field['cc_planting_method']}'";
			$cc_date_planted_ts = strtotime("{$field['cc_month']}/{$field['cc_day']}/{$field['cc_year']}");
			$cc_fall_quality = empty($field['cc_quality_fall']) ? 'NULL' : $field['cc_quality_fall'];
			$cc_spring_quality = empty($field['cc_quality_spring']) ? 'NULL' : $field['cc_quality_spring'];
			$cc_term_method = empty($field['cc_term_method']) ? 'NULL' : "'{$field['cc_term_method']}'";
			$cc_term_chem = empty($field['cc_term_chem']) ? 'NULL' : "'{$field['cc_term_chem']}'";
			$cc_carrier = empty($field['cc_carrier']) ? 'NULL' : "'{$field['cc_carrier']}'";
			$cc_spray_count = empty($field['cc_sprayed']) ? 'NULL' : $field['cc_sprayed'];
			$cc_term_temp = preg_replace('/[^\d+]/','',$field['cc_term_temp']);
			$cc_term_temp = empty($cc_term_temp) ? 'NULL' : intval($cc_term_temp);
			$cc_term_weather = empty($field['cc_term_weather']) ? 'NULL' : "'{$field['cc_term_weather']}'";
			$cc_term_date_ts = strtotime("{$field['cc_term_month']}/{$field['cc_term_day']}/{$field['cc_term_year']}");
			$cc_term_success = empty($field['cc_success_term']) ? 'NULL' : "'{$field['cc_success_term']}'";
			$cc_yield = preg_replace('/[^\d+]/','',$field['cc_yield']);
			$cc_yield = empty($cc_yield) ? 'NULL' : intval($cc_yield);
			$cc_comments = empty($field['cc_comments']) ? 'NULL' : "'{$field['cc_comments']}'";
			$ct_acres = preg_replace('/[^\d+]/','',$field['ct_acres']);
			$ct_acres = empty($ct_acres) ? 'NULL' : intval($ct_acres);
			$ct_previous = empty($field['ct_previous']) ? 'NULL' : "'{$field['ct_previous']}'";
			$ct_current = empty($field['ct_current']) ? 'NULL' : "'{$field['ct_current']}'";
			$ct_comments = empty($field['ct_comments']) ? 'NULL' : "'{$field['ct_comments']}'";
			$pc_pest = empty($field['pc_pest']) ? 'NULL' : "'{$field['pc_pest']}'";
			$pc_threshold = empty($field['pc_threshold']) ? 'NULL' : "'{$field['pc_threshold']}'";
			$pc_method = empty($field['pc_method']) ? 'NULL' : "'{$field['pc_method']}'";
			$pc_CAP = empty($field['pc_chem_app_rate']) ? 'NULL' : "'{$field['pc_chem_app_rate']}'";
			$pc_application_date_ts = strtotime("{$field['pc_app_month']}/{$field['pc_app_day']}/{$field['pc_app_year']}");
			$pc_comments = empty($field['pc_comments']) ? 'NULL' : "'{$field['pc_comments']}'";
			$this->query("
				UPDATE field
				SET
					field_name=$field_name,year=$year,acres=$acres,herbicide_used=$herbicide_used,preceding_crop=$preceding_crop,current_crop=$current_crop,date_planted_ts=$date_planted_ts,lime_applied=$lime_applied,comments=$comments,
					f_N_PSNT=$f_N_PSNT,f_brayp1=$f_brayp1,f_K=$f_K,f_NH4=$f_NH4,f_soilph=$f_soilph,f_bufferedph=$f_bufferedph,f_comments=$f_comments,
					na_N=$na_N,na_P=$na_P,na_K=$na_K,na_manure=$na_manure,na_type=$na_type,na_mN=$na_mN,na_mP=$na_mP,na_mK=$na_mK,na_comments=$na_comments,
					cc_family=$cc_family,cc_used=$cc_used,cc_rate=$cc_rate,cc_seed_cost=$cc_seed_cost,cc_cost_share=$cc_cost_share,cc_planting_method=$cc_planting_method,cc_date_planted_ts=$cc_date_planted_ts,cc_fall_quality=$cc_fall_quality,cc_spring_quality=$cc_spring_quality,cc_term_method=$cc_term_method,cc_term_chem=$cc_term_chem,cc_carrier=$cc_carrier,cc_spray_count=$cc_spray_count,cc_term_temp=$cc_term_temp,cc_term_weather=$cc_term_weather,cc_term_date_ts=$cc_term_date_ts,cc_term_success=$cc_term_success,cc_yield=$cc_yield,cc_comments=$cc_comments,
					ct_acres=$ct_acres,ct_previous=$ct_previous,ct_current=$ct_current,ct_comments=$ct_comments,
					pc_pest=$pc_pest,pc_threshold=$pc_threshold,pc_method=$pc_method,pc_CAP=$pc_CAP,pc_application_date_ts=$pc_application_date_ts,pc_comments=$pc_comments
				WHERE field_id={$field['field_id']}
			");
			if($this->updateOK()) {
				$this->query("
					UPDATE farm 
					SET last_mod_by=$last_mod_by, last_mod_ts=UNIX_TIMESTAMP() 
					WHERE farm_id={$field['farm_id']}
				");
				return true;
			} else return false;
		}
		
		public function updateFarm($farm, $user_email) {
			// SQL injection and escaping
			foreach($farm as &$f) $f = $this->getEscapedSQLString(trim($f));
			
			// prepare fields
			$name = "'{$farm['name']}'";
			$last_mod_by = "'{$user_email}'";
			$contact_fn = "'{$farm['contact_fn']}'";
			$contact_ln = "'{$farm['contact_ln']}'";
			$contact_phone = empty($farm['contact_phone']) ? 'NULL' : "'{$farm['contact_phone']}'";
			$contact_mobile = empty($farm['contact_mobile']) ? 'NULL' : "'{$farm['contact_mobile']}'";
			$contact_email = empty($farm['contact_email']) ? 'NULL' : "'{$farm['contact_email']}'";
			$address = empty($farm['address']) ? 'NULL' : "'{$farm['address']}'";
			$city = empty($farm['city']) ? 'NULL' : "'{$farm['city']}'";
			$state = empty($farm['state']) ? 'NULL' : "'{$farm['state']}'";
			$zip = empty($farm['zip']) ? 'NULL' : "'{$farm['zip']}'";
			$county = empty($farm['county']) ? 'NULL' : "'{$farm['county']}'";
			$basin = empty($farm['basin']) ? 'NULL' : "'{$farm['basin']}'";
			$huc = empty($farm['huc']) ? 'NULL' : "'{$farm['huc']}'";
			
			$this->query("
				UPDATE farm
				SET 
					name=$name,last_mod_by=$last_mod_by,last_mod_ts=UNIX_TIMESTAMP(),contact_fn=$contact_fn,contact_ln=$contact_ln,
					contact_phone=$contact_phone,contact_mobile=$contact_mobile,contact_email=$contact_email,
					address=$address,city=$city,state=$state,zip=$zip,county=$county,basin=$basin,huc=$huc
				WHERE farm_id={$farm['farm_id']}
			");
			if($this->updateOK()) return true;
			else return false;
		}
		
		public function fetchAll($farm_id) {
			$this->query("SELECT farm.*, field.* FROM farm INNER JOIN field USING(farm_id) WHERE farm_id = $farm_id");
			if($this->queryOK()) {
				$farm = array();
				while($row = $this->next()) $farm[] = $row;
				return $farm;
			} else return NULL;
		}
		
		public function fetchFarm($farm_id) {
			$this->query("SELECT * FROM farm WHERE farm_id = $farm_id LIMIT 1");
			if($this->queryOK()) return $this->next();
			else return NULL;
		}
		
		public function fetchField($field_id) {
			$this->query("SELECT * FROM field WHERE field_id = $field_id LIMIT 1");
			if($this->queryOK()) {
				$field = $this->next();
				// build m/d/Y out of timestamps
				foreach($this->_field_ts_map as $ts_field => $prefix) {
					$date = date('m/d/Y', $field[$ts_field]);
					list($field["{$prefix}_month"], $field["{$prefix}_day"], $field["{$prefix}_year"]) = explode('/', $date);
				}
				return $field;
			}
			else return NULL;
		}
		
	}